package com.ruoyi.web.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;

/**
 * POI_Excel 辅助类
 * by.Treks
 */
public class POIExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(POIExcelUtil.class);

    /**
     * 自适应列宽
     * @param sheet sheet对象
     * @param firstColumn 起始列，从0开始
     * @param lastColumn 结束列
     */
    public static void autoSizeColumn(Sheet sheet, int firstColumn, int lastColumn) {
        for (int i = firstColumn; i <= lastColumn; i++) {
            //列宽度自适应
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
    }

    /**
     * 获取默认单元格样式
     * @param workbook workbook对象
     */
    public static CellStyle getCellStyle(Workbook workbook){
        return workbook.createCellStyle();
    }

    /**
     * 单元格居中
     * @param style style对象
     */
    public static void standardCellStyle1(CellStyle style){
        //设置水平对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐方式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
    }

    /**
     * 四向边框
     * @param style style对象
     */
    public static void standardCellStyle2(CellStyle style){
        //边框样式及颜色
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    }

    /**
     * 微软雅黑10号字体
     * @param style style对象
     */
    public static void standardCellStyle3(CellStyle style, Workbook workbook){
        //字体
        Font dataFont = workbook.createFont();
        dataFont.setFontName("微软雅黑");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        style.setWrapText(true);  // 单元格内容折行
    }

    /**
     * 批量设置单元格格式
     * @param sheet sheet对象
     * @param style style对象
     * @param firstRow 起始行，从0开始
     * @param lastRow 结束行
     * @param firstCol 起始列，从0开始
     * @param lastCol 结束列
     */
    public static void setStyleArea(Sheet sheet, CellStyle style, int firstRow, int lastRow, int firstCol, int lastCol) {
        for (int j = firstRow; j <= lastRow; j++) {
            Row row = sheet.getRow(j);
            if (row == null) continue;
            for (int i = firstCol; i <= lastCol; i++) {
                Cell cell = row.getCell(i);
                if (cell == null) cell = row.createCell(i);
                cell.setCellStyle(style);
            }
        }
    }

    /**
     * 设置单元格合并
     * @param sheet sheet对象
     * @param firstRow 起始行，从0开始
     * @param lastRow 结束行
     * @param firstCol 起始列，从0开始
     * @param lastCol 结束列
     */
    public static void setMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 将制作好的excel导出到前端
     */
    public static void exportExcel(Workbook wb, HttpServletResponse response)
    {
        try
        {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            wb.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            log.error("导出Excel异常{}", e.getMessage());
        }
        finally
        {
            IOUtils.closeQuietly(wb);
        }
    }

}
